-- Recursively deletes all children of the specified hierarchy key.  After this function returns,
-- all child documents, along with all associated hierarchy keys, will be deleted, resulting
-- in just a leaf-node, which can then be deleted by asi_DocumentDeleteDocument.
CREATE PROCEDURE [dbo].[asi_DocumentDeleteHierarchy]
    @hierarchyKey uniqueidentifier,
    @purgeVersions bit = 0
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @ChildrenTable TABLE
    (
        [HierarchyKey] uniqueidentifier,
        [DocumentVersionKey] uniqueidentifier
    )

    INSERT INTO @ChildrenTable
        SELECT h.[HierarchyKey], d.[DocumentVersionKey]
          FROM [dbo].[Hierarchy] h
               INNER JOIN [dbo].[DocumentMain] d ON h.UniformKey = d.[DocumentVersionKey]
         WHERE h.[ParentHierarchyKey] = @hierarchyKey

    DECLARE @childHierarchyKey uniqueidentifier
    DECLARE @documentVersionKey uniqueidentifier

    WHILE EXISTS (SELECT 1 FROM @ChildrenTable)
    BEGIN
        SELECT TOP 1 @childHierarchyKey = HierarchyKey, @documentVersionKey = DocumentVersionKey FROM @ChildrenTable
        EXEC [asi_DocumentDeleteHierarchy] @childHierarchyKey, @purgeVersions
        EXEC [asi_DocumentDeleteDocument] @documentVersionKey, @purgeVersions
        DELETE FROM @ChildrenTable WHERE HierarchyKey = @childHierarchyKey
    END
    SET NOCOUNT OFF
END

GO